set hive.exec.dynamic.partition=true;            --  动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=500;         --  总共
set hive.exec.max.dynamic.partitions.pernode=500; --  每个节点
with tab_collect_all as ( --collect表信息
select
 waybill_no --单号
,scan_time  --扫描时间
,scan_type  --扫描类型
,scan_site_code  --扫描网点
,nvl(store_code, '10087777') as store_code   --门店编码
,store_name   --门店名称
,nvl(business_id,'10086666') as business_id  --品牌编码
,source       --机柜
,scan_date    --扫描日期
,input_time
,input_date
,writeback_flag
,case when scan_type in ('出库扫描','快件取出扫描') then date_format(scan_time,'yyyy-MM-dd HH') 
      when scan_type ='入库扫描' then date_format(input_time,'yyyy-MM-dd HH') --因为要和末端监控入库量对齐 所以用inpute_time
      end as scan_hm 
from (
    select
    *,row_number() over(partition by waybill_no,scan_site_code,store_code,business_id,input_date,scan_type order by scan_time) as rk
    from
    (
    select
    waybill_no    --单号
    ,`source`     --机柜
    ,scan_time    --扫描时间
    ,scan_type    --扫描类型
    ,scan_site_code --扫描网点
    ,post_station_code as store_code --门店编码
    ,storage_address  as store_name--门店名称
    , case when real_oper_source_code is null then '10086666'
           when real_oper_source_code = '45' then '94'
           when real_oper_source_code = '82' then '95'
           when real_oper_source_code = '2' then '97' --邻里品牌编码出库扫描97 入库扫描2
           else real_oper_source_code END as business_id --品牌编码
    ,input_time
    ,date(scan_time) as  scan_date
    ,date(input_time) as input_date
    ,case when scan_type='入库扫描' and  writeback_flag=1 then 1
          when scan_type='出库扫描' and  writeback_flag=0 then 1
     else 0 end as writeback_flag
    from  jms_dwd.dwd_tab_barscan_collect_base_dt
    where dt = '{{ execution_date  | cst_ds }}' 
          and (scan_type ='入库扫描' or (date(scan_time)='{{ execution_date  | cst_ds }}' and scan_type='出库扫描'))
          and length (waybill_no) <= 15
          and scan_site_code not like 'B%'
          and scan_site_code is not null          
)t )t1 where rk=1
),
t_store_info as (  --门店+门店信息关联确认
select *
from (
    select
          nvl(stor.business_id, '10086666') as business_id
        , stor.courier_station_code  as store_code--门店编码
        , stor.name as store_name --门店名称
        , stor.phone as store_phone
        , stor.detailed_address as  store_address
        , row_number() over (partition by stor.courier_station_code, stor.business_id order by stor.update_time,stor.create_time) rk ---按照品牌+门店编码去重
    from  jms_dim.dim_tab_agent_point_base  stor
    where courier_station_code is not null
    ) t_union_point_secret_in
where rk = 1
)

insert overwrite table jms_dm.dm_end_dispatch_inout_hour_dt
select
date_time  --查询日期
,max(nvl(net.virt_code,net.agent_code ))  as agent_code --虚拟代理区编码
,max(nvl(net.virt_name,net.agent_name ))  as agent_name --虚拟代理区名称
,max(net.agent_code )  as agent_code --代理区编码
,max(net.agent_name )  as agent_name --代理区名称
,max(net.fran_code  )  as fran_code  --加盟商code
,max(net.fran_name  )  as fran_name  --加盟商名称
,main.scan_site_code                 --网点编码
,max(net.name)         as scan_site_name --网点名称
,main.business_id --品牌id
,max(bus.business_name) as business_name --品牌名称
,main.source --末端类型
,max(brand.build_type) as build_type --建设类型
,max(case when stor.store_address like '%学院%' then '校园驿站'
          when stor.store_address like '%学校%' then '校园驿站'
          when stor.store_address like '%大学%' then '校园驿站'
          when stor.store_address like '%镇%' then '乡镇驿站'
          when stor.store_address like '%乡%' then '乡镇驿站'
          when stor.store_address like '%村%' then '乡镇驿站'
          when stor.store_address like '%庄%' then '乡镇驿站'
 else '城市驿站'
 end) as store_type --门店属性
,main.store_code --门店编码
,max(nvl(stor.store_address,main.store_name)) as store_name --门店名称
,sum(if(main.scan_type='入库扫描',scan_cnt,0)) as scan_cnt_in  --入库总量
,sum(if(main.scan_type='出库扫描',scan_cnt,0)) as scan_cnt_out --出库总量
,sum(in_time_stage00) as in_time_stage00      --入库量00-01
,sum(out_time_stage00) as out_time_stage00    --出库量00-01
,sum(in_time_stage01) as in_time_stage01      --入库量01-02
,sum(out_time_stage01) as out_time_stage01    --出库量01-02
,sum(in_time_stage02) as in_time_stage02      --入库量02-03
,sum(out_time_stage02) as out_time_stage02    --出库量02-03
,sum(in_time_stage03) as in_time_stage03      --入库量03-04
,sum(out_time_stage03) as out_time_stage03    --出库量03-04
,sum(in_time_stage04) as in_time_stage04      --入库量04-05
,sum(out_time_stage04) as out_time_stage04    --出库量04-05
,sum(in_time_stage05) as in_time_stage05      --入库量05-06
,sum(out_time_stage05) as out_time_stage05    --出库量05-06
,sum(in_time_stage06) as in_time_stage06      --入库量06-07
,sum(out_time_stage06) as out_time_stage06    --出库量06-07
,sum(in_time_stage07) as in_time_stage07      --入库量07-08
,sum(out_time_stage07) as out_time_stage07    --出库量07-08
,sum(in_time_stage08) as in_time_stage08      --入库量08-09
,sum(out_time_stage08) as out_time_stage08    --出库量08-09
,sum(in_time_stage09) as in_time_stage09      --入库量09-10
,sum(out_time_stage09) as out_time_stage09    --出库量09-10
,sum(in_time_stage10) as in_time_stage10      --入库量10-11
,sum(out_time_stage10) as out_time_stage10    --出库量10-11
,sum(in_time_stage11) as in_time_stage11      --入库量11-12
,sum(out_time_stage11) as out_time_stage11    --出库量11-12
,sum(in_time_stage12) as in_time_stage12      --入库量12-13
,sum(out_time_stage12) as out_time_stage12    --出库量12-13
,sum(in_time_stage13) as in_time_stage13      --入库量13-14
,sum(out_time_stage13) as out_time_stage13    --出库量13-14
,sum(in_time_stage14) as in_time_stage14      --入库量14-15
,sum(out_time_stage14) as out_time_stage14    --出库量14-15
,sum(in_time_stage15) as in_time_stage15      --入库量15-16
,sum(out_time_stage15) as out_time_stage15    --出库量15-16
,sum(in_time_stage16) as in_time_stage16      --入库量16-17
,sum(out_time_stage16) as out_time_stage16    --出库量16-17
,sum(in_time_stage17) as in_time_stage17      --入库量17-18
,sum(out_time_stage17) as out_time_stage17    --出库量17-18
,sum(in_time_stage18) as in_time_stage18      --入库量18-19
,sum(out_time_stage18) as out_time_stage18    --出库量18-19
,sum(in_time_stage19) as in_time_stage19      --入库量19-20
,sum(out_time_stage19) as out_time_stage19    --出库量19-20
,sum(in_time_stage20) as in_time_stage20      --入库量20-21
,sum(out_time_stage20) as out_time_stage20    --出库量20-21
,sum(in_time_stage21) as in_time_stage21      --入库量21-22
,sum(out_time_stage21) as out_time_stage21    --出库量21-22
,sum(in_time_stage22) as in_time_stage22      --入库量22-23
,sum(out_time_stage22) as out_time_stage22    --出库量22-23
,sum(in_time_stage23) as in_time_stage23      --入库量23-24
,sum(out_time_stage23) as out_time_stage23    --出库量23-24
,date_time as dt
from 
(
    select 
     scan_site_code
    ,store_code
    ,store_name
    ,business_id
    ,source
    ,scan_hm
    ,substr(scan_hm,1,10) as date_time
    ,scan_cnt
    ,scan_type
    ,if(substr(scan_hm,12,2)='00' and scan_type='入库扫描', scan_cnt,0) as in_time_stage00
    ,if(substr(scan_hm,12,2)='01' and scan_type='入库扫描', scan_cnt,0) as in_time_stage01
    ,if(substr(scan_hm,12,2)='02' and scan_type='入库扫描', scan_cnt,0) as in_time_stage02
    ,if(substr(scan_hm,12,2)='03' and scan_type='入库扫描', scan_cnt,0) as in_time_stage03
    ,if(substr(scan_hm,12,2)='04' and scan_type='入库扫描', scan_cnt,0) as in_time_stage04
    ,if(substr(scan_hm,12,2)='05' and scan_type='入库扫描', scan_cnt,0) as in_time_stage05
    ,if(substr(scan_hm,12,2)='06' and scan_type='入库扫描', scan_cnt,0) as in_time_stage06
    ,if(substr(scan_hm,12,2)='07' and scan_type='入库扫描', scan_cnt,0) as in_time_stage07
    ,if(substr(scan_hm,12,2)='08' and scan_type='入库扫描', scan_cnt,0) as in_time_stage08
    ,if(substr(scan_hm,12,2)='09' and scan_type='入库扫描', scan_cnt,0) as in_time_stage09
    ,if(substr(scan_hm,12,2)='10' and scan_type='入库扫描', scan_cnt,0) as in_time_stage10
    ,if(substr(scan_hm,12,2)='11' and scan_type='入库扫描', scan_cnt,0) as in_time_stage11
    ,if(substr(scan_hm,12,2)='12' and scan_type='入库扫描', scan_cnt,0) as in_time_stage12
    ,if(substr(scan_hm,12,2)='13' and scan_type='入库扫描', scan_cnt,0) as in_time_stage13
    ,if(substr(scan_hm,12,2)='14' and scan_type='入库扫描', scan_cnt,0) as in_time_stage14
    ,if(substr(scan_hm,12,2)='15' and scan_type='入库扫描', scan_cnt,0) as in_time_stage15
    ,if(substr(scan_hm,12,2)='16' and scan_type='入库扫描', scan_cnt,0) as in_time_stage16
    ,if(substr(scan_hm,12,2)='17' and scan_type='入库扫描', scan_cnt,0) as in_time_stage17
    ,if(substr(scan_hm,12,2)='18' and scan_type='入库扫描', scan_cnt,0) as in_time_stage18
    ,if(substr(scan_hm,12,2)='19' and scan_type='入库扫描', scan_cnt,0) as in_time_stage19
    ,if(substr(scan_hm,12,2)='20' and scan_type='入库扫描', scan_cnt,0) as in_time_stage20
    ,if(substr(scan_hm,12,2)='21' and scan_type='入库扫描', scan_cnt,0) as in_time_stage21
    ,if(substr(scan_hm,12,2)='22' and scan_type='入库扫描', scan_cnt,0) as in_time_stage22
    ,if(substr(scan_hm,12,2)='23' and scan_type='入库扫描', scan_cnt,0) as in_time_stage23
    ,if(substr(scan_hm,12,2)='00' and scan_type='出库扫描', scan_cnt,0) as out_time_stage00
    ,if(substr(scan_hm,12,2)='01' and scan_type='出库扫描', scan_cnt,0) as out_time_stage01
    ,if(substr(scan_hm,12,2)='02' and scan_type='出库扫描', scan_cnt,0) as out_time_stage02
    ,if(substr(scan_hm,12,2)='03' and scan_type='出库扫描', scan_cnt,0) as out_time_stage03
    ,if(substr(scan_hm,12,2)='04' and scan_type='出库扫描', scan_cnt,0) as out_time_stage04
    ,if(substr(scan_hm,12,2)='05' and scan_type='出库扫描', scan_cnt,0) as out_time_stage05
    ,if(substr(scan_hm,12,2)='06' and scan_type='出库扫描', scan_cnt,0) as out_time_stage06
    ,if(substr(scan_hm,12,2)='07' and scan_type='出库扫描', scan_cnt,0) as out_time_stage07
    ,if(substr(scan_hm,12,2)='08' and scan_type='出库扫描', scan_cnt,0) as out_time_stage08
    ,if(substr(scan_hm,12,2)='09' and scan_type='出库扫描', scan_cnt,0) as out_time_stage09
    ,if(substr(scan_hm,12,2)='10' and scan_type='出库扫描', scan_cnt,0) as out_time_stage10
    ,if(substr(scan_hm,12,2)='11' and scan_type='出库扫描', scan_cnt,0) as out_time_stage11
    ,if(substr(scan_hm,12,2)='12' and scan_type='出库扫描', scan_cnt,0) as out_time_stage12
    ,if(substr(scan_hm,12,2)='13' and scan_type='出库扫描', scan_cnt,0) as out_time_stage13
    ,if(substr(scan_hm,12,2)='14' and scan_type='出库扫描', scan_cnt,0) as out_time_stage14
    ,if(substr(scan_hm,12,2)='15' and scan_type='出库扫描', scan_cnt,0) as out_time_stage15
    ,if(substr(scan_hm,12,2)='16' and scan_type='出库扫描', scan_cnt,0) as out_time_stage16
    ,if(substr(scan_hm,12,2)='17' and scan_type='出库扫描', scan_cnt,0) as out_time_stage17
    ,if(substr(scan_hm,12,2)='18' and scan_type='出库扫描', scan_cnt,0) as out_time_stage18
    ,if(substr(scan_hm,12,2)='19' and scan_type='出库扫描', scan_cnt,0) as out_time_stage19
    ,if(substr(scan_hm,12,2)='20' and scan_type='出库扫描', scan_cnt,0) as out_time_stage20
    ,if(substr(scan_hm,12,2)='21' and scan_type='出库扫描', scan_cnt,0) as out_time_stage21
    ,if(substr(scan_hm,12,2)='22' and scan_type='出库扫描', scan_cnt,0) as out_time_stage22
    ,if(substr(scan_hm,12,2)='23' and scan_type='出库扫描', scan_cnt,0) as out_time_stage23
    from 
    (
        select scan_site_code,store_code,max(store_name) as store_name,business_id,source,scan_hm,scan_type,count(1) scan_cnt
        from tab_collect_all 
        group by scan_site_code,store_code,business_id,source,scan_hm,scan_type
    )t 
)main
left join jms_dim.dim_network_whole_massage net on  net.code=main.scan_site_code
left join t_store_info stor on stor.business_id=main.business_id and stor.store_code=main.store_code
left join (select business_id, max(business_name) as business_name from jms_dim.dim_tab_terminal_secret_base group by business_id) bus on bus.business_id=main.business_id
left join
(  --获取建设类型
    select  business_id,courier_station_code--,build_type
    ,case when build_type =1 then '自建' when  build_type =2 then '合作'  else build_type end as build_type
    from jms_dim.dim_ide_tab_end_brand_info_base
    where status in(1,5) --1已认证
    group by business_id,courier_station_code,build_type
) brand on  brand.business_id=main.business_id and  brand.courier_station_code=main.store_code
group by main.scan_site_code,main.store_code,main.business_id,main.source,date_time
distribute by 1;

